package com.ejoysoft.wlanyz.business;

import com.ejoysoft.auth.MD5;
import com.ejoysoft.common.*;
import com.ejoysoft.common.exception.UserUnitIdException;
import com.ejoysoft.wlanyz.system.Unit;
import com.ejoysoft.wlanyz.system.User;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;

import java.util.ArrayList;
import java.util.Vector;
import java.util.HashMap;
import java.util.Date;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

public class Paper {
	private Globa globa;
    private DbConnect db;
    

    public Paper() {
    }

    public Paper(Globa globa) {
        this.globa = globa;
        db = globa.db;
    }

    public Paper(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName = "ac_cad_info";
    String strTableName2 = "ac_class_associate";


    //增加用户
    public boolean add(String XML) throws Exception{
    	//String strTempId = UID.getID();
        Readxml s = new Readxml(globa,false);
       return s.read(globa.loginName,strAcName,XML) ;
    }
    public boolean add1(String XML,String ACName) throws SQLException{
    	//String strTempId = UID.getID();
    	try{
    	String sql = " delete from "+strTableName+" where acname ='"+ACName+"' ";
    	System.out.println("################"+sql);
    	db.setAutoCommit(false);
    	db.executeUpdate(sql);
        Globa.logger0("删除图纸信息", globa.loginName, globa.loginIp, sql, "图纸管理", globa.unitCode);
       Readxml s = new Readxml(globa,false);
        if(!s.read(globa.loginName,strAcName,XML)){db.rollback();db.setAutoCommit(true);return false;}
        else db.commit();db.setAutoCommit(true);return true;
       //if(s.read(globa.loginName,strAcName,XML))return true;
       //else{db.rollback();return false;}
    	}catch (Exception ee) {
        	db.rollback();
            ee.printStackTrace();
            return false;
        }
    }
    public boolean delete(String where)throws SQLException {
        try {
            String sql = "DELETE FROM " + strTableName + "  ".concat(where);
            db.setAutoCommit(false);
            db.executeUpdate(sql);
            //ɾ���û�ӳ����?
          //  delUnitUser(where);
            Globa.logger0("删除图纸信息", globa.loginName, globa.loginIp, sql, "图纸管理", globa.unitCode);
            db.commit();db.setAutoCommit(true);return true ;
        } catch (Exception ee) {
        	db.rollback();
            ee.printStackTrace();
            return false;
        }
    }
    public boolean update(String XML,String AcName) throws SQLException {
    	try{
			 String strSql = "DELETE  FROM " + strTableName + "  WHERE ACNAME= '"+AcName+"' ";
			 db.setAutoCommit(false);
			 //con.setAutoCommit(false);
			db.executeQuery(strSql);System.out.println("**************8888"+strSql);
			Readxml s = new Readxml(globa,false);
	        if(s.read(globa.loginName,strAcName,XML)){
	        	db.commit();
	        	db.setAutoCommit(true);
	        	return true ;
	        	}
	        else{ db.rollback();System.out.println("$$$$$$$$$$$$$$$$$$$$$$4444");return false;}
			}catch (Exception e) {
				db.rollback();
		        e.printStackTrace();
		        return false;
		        }
    	
    }
    
    public boolean isexist(String where)
    {
      try{
    	  String sql = "select * from "+strTableName+" where acname in (select strid from "+strTableName2 +"  "+where+" ) ";
    	  //System.out.println("000000000000000"+sql);
    	  ResultSet rs = db.executeQuery(sql);
    	  if(rs!=null&& rs.next()){
    		  rs.close();
    		  return true;
    	  }
    	  else 
    		  rs.close();
    		  return false;
          }
      catch (Exception ee) {
          return false;
      }
    }
//返回单条记录
    public Paper show(String where) {
        try {
            String strSql = "select distinct acname,cadpic from  " + strTableName + "  ".concat(where);
            System.err.println(strSql);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }
    //返回图纸名称
    public String getImgName(String where )
    {
    	try{
    		String sql = " select distinct cadpic from "+strTableName+" "+where;
    		//System.out.println("000000000"+sql);
    		ResultSet rs = db.executeQuery(sql);
    		if (rs != null && rs.next())
    		{
    			String imgname = rs.getString("cadpic");
    			rs.close();
    		return imgname;
    		}
    		else return "";
    	}catch (Exception ee) {
            return "";
        }
    }
    
  //返回热点id对应的热点名
    public String getAcId(String where) {
        try {
            String strSql = "select * from  " + strTableName2 + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
            {
            	String ACID = rs.getString("strid");
            	rs.close();
                return ACID;
            }
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }


    //记录集转化为对象
    public Paper load(ResultSet rs, boolean isView) {
        Paper theBean = new Paper();
        try {
           // theBean.setStrId(rs.getString("strId"));
            theBean.setAcName(rs.getString("AcName"));
           // theBean.setCadName(rs.getString("CadName"));
            theBean.setCadPic(rs.getString("CadPic"));
           // theBean.setFloor(rs.getString("intFloor")); 
           // theBean.setStrCreator(rs.getString("strCreator"));
          //  theBean.setdCreatDate(rs.getString("dtCreateTime"));
        } catch (Exception e) {
            e.printStackTrace();        
        }
        return theBean;
    }
    
    //记录集转化为对象
    public Paper load2(ResultSet rs, boolean isView) {
        Paper theBean = new Paper();
        try {
        	theBean.setAcName(rs.getString(1));
        	theBean.setStrId(rs.getString(2));
            theBean.setStrCity(rs.getString(3));
            theBean.setStrCountry(rs.getString(4));
            theBean.setClassOneAc(rs.getString(5));
            theBean.setClssTwoAc(rs.getString(6));
           // theBean.setFloor(rs.getString(7));
            //theBean.setCadName(rs.getString(8));
  
        } catch (Exception e) {
            e.printStackTrace();        
        }
        return theBean;
    }
    public Paper load22(ResultSet rs, boolean isView) {
        Paper theBean = new Paper();
        try {
        	
        	theBean.setCadPic(rs.getString(1));
        	theBean.setAcName(rs.getString(2));
            //theBean.setStrCity(rs.getString(3));
            //theBean.setStrCountry(rs.getString(4));
            //theBean.setClassOneAc(rs.getString(5));
           // theBean.setClssTwoAc(rs.getString(6));
           // theBean.setFloor(rs.getString(7));
            //theBean.setCadName(rs.getString(8));
  
        } catch (Exception e) {
            e.printStackTrace();        
        }
        return theBean;
    }

  //查询符合条件的记录总数
    public int getCount(String where) {
        int count = 0;
        try {
            String sql = "SELECT count(distinct acname) FROM " + strTableName + " ";
            if (where.length() > 0) {
                where = where.toLowerCase();
                if (where.indexOf("order") > 0)
                    where = where.substring(0, where.lastIndexOf("order"));
                sql = String.valueOf(sql) + String.valueOf(where);
            } 
            //System.out.println("*************"+sql);
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            System.out.println(ee);           
            return count;
        }
    }


//获取二级热点楼层
    
    public Vector<String> getFloors(String where)
    {
    	Vector<String> beans = new Vector<String>();
    	try{
    		String sql = " select distinct intfloor from "+strTableName +"" +where+" and imgext = 1"; 
    		sql+="  order by intfloor ";
    		 ResultSet rs = db.executeQuery(sql);
             if (rs != null && rs.next()) {
                 do {
                     beans.add(rs.getString("intfloor")); 
                 } while (rs.next());
             }
             rs.close();
    	}catch (Exception ee) {
            ee.printStackTrace();
        }
    	db.closeCon();
    	return beans;
    }
    //判断上传的图片名称是否正确
    public boolean isImgExt(String ImgName,String ACname)
    {
    	DbConnect dbConnect = new DbConnect();
    	//System.out.println(ImgName+"DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD"+ACname);
    	try{
    		String sql = " select * from "+strTableName+" where cadpic = '"+ImgName+"' and acname = " +
    				"'"+ACname+"'";
    		String sqlString = "select cadpic from "+strTableName+" ";
    		ResultSet rb = db.executeQuery(sqlString);
    		if (rb != null && rb.next()){
    			do{
    		}while(rb.next());
    			}
    		ResultSet rs = dbConnect.executeQuery(sql);
            if (rs != null && rs.next()){
        		System.out.println("000000000"+rs.getString("cadpic"));
        		rs.close();
        		dbConnect.closeCon();
        		return true;
            }
            else {rs.close();dbConnect.closeCon();return false;}
    	}catch (Exception ee) {
            ee.printStackTrace();
            dbConnect.closeCon();
            return false;
        }
    }
    //设置图片是否上传
    public void setImgflag(String FileName,int flag,String acname)
    {
    	try{
    		String sql = " update "+strTableName+" set imgext = "+flag+" where cadpic = '"+FileName+"' and acname ='"+acname+"'";
    		System.out.println("00000000000000"+sql);
    		ResultSet rs = db.executeQuery(sql);
    		rs.close();
    	}catch (Exception ee) {
            ee.printStackTrace();
    	}
    	 db.closeCon();
           
    }
    public void setImgflag1(String FileName,int flag,String acname)
    {
    	try{
    		String sql = " update "+strTableName+" set imgext = "+flag+" where cadpic = '"+FileName+"' and acname ='"+acname+"'";
    		System.out.println("00000000000000"+sql);
    		ResultSet rs = db.executeQuery(sql);
    		rs.close();
    	}catch (Exception ee) {
            ee.printStackTrace();
    	}
    	 db.closeCon();
           
    }
 
  //列表记录集
    public Vector<Paper> list(String where, int startRow, int rowCount) {
        Vector<Paper> beans = new Vector<Paper>();
        try {
            String sql = "SELECT distinct a.acname,b.strid,b.strcity,b.strcountry,b.classoneac,b.classtwoac FROM  " + strTableName + " a , " + strTableName2 + " b  ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
             String orderWhereString = " and  a.acname = b.strid order by b.strcity,b.strcountry,b.classoneac,b.classtwoac";
             sql += orderWhereString;
           
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            System.out.println("&&&&&&&&&&&&&&&77"+sql);
             ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                    Paper theBean = new Paper();
                    theBean = load2(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }finally{
        	db.closeCon();
        }
        return beans;
    }
    
  //查询时调用的方法
    public Vector<Paper> list4(String where , int startRow, int rowCount) {
        Vector<Paper> beans = new Vector<Paper>();
        try {
        	String sql2 = "SELECT distinct a.acname,b.strid,b.strcity,b.strcountry,b.classoneac,b.classtwoac FROM  " + strTableName + " a , " + strTableName2 + " b  ";
        	sql2 += where;
        	sql2 += " and b.strid = a.acname";
        	Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            System.out.println("&&&&&&&&&&&&&&&77"+sql2);
             ResultSet rs = s.executeQuery(sql2);
        	
        	if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                    Paper theBean = new Paper();
                    theBean = load2(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }finally{
        	db.closeCon();
        }
        return beans;
    }
    
    
    public Vector<Paper> list11(String where, int startRow, int rowCount) {
        Vector<Paper> beans = new Vector<Paper>();
        try {
            String sql = "SELECT distinct cadpic,acname FROM  " + strTableName + "   ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
             String orderWhereString = "  order by cadpic ";
             sql += orderWhereString;
           
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
             ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                    Paper theBean = new Paper();
                    theBean = load22(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }finally{
        	db.closeCon();
        }
        return beans;
    }
    
  //列表记录集
    public Vector<Paper> list2(String where, int startRow, int rowCount) {
        Vector<Paper> beans = new Vector<Paper>();
        try {
            String sql = "SELECT *  FROM  " + strTableName + " ";
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
//            System.out.println(""+sql);
            ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                    Paper theBean = new Paper();
                    theBean = load(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }finally{
        	db.closeCon();
        }
        return beans;
    }
    //调出所有热点的城市名称
    public ArrayList<Object[]> getAllclassoneByName( String where )
    {
    	DbConnect dbConnect = new DbConnect();
    	ArrayList<Object[]>  beans = new ArrayList<Object[]>();
    	String sql = "select distinct(classoneac) from "+strTableName2+" ";
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	ResultSet re = dbConnect.executeQuery(sql);
    	try {
			if(re != null && re.next())
			{
				do{
					Object[] theBean = new Object[2];
					String classoneac = re.getString("classoneac");
					theBean[0] = classoneac;
					theBean[1] = this.getAllstrclasstwoByName(classoneac);
					beans.add(theBean);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbConnect.closeCon();
    	return beans;
    }
    //调出城市所有二级热点名称
    public ArrayList<String> getAllstrclasstwoByName( String classoneac)
    {   DbConnect dbConnect = new DbConnect();
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(classtwoac) from "+strTableName2+" ";
    	String where = " where classoneac='"+classoneac+"'";
    	sql = sql+where;
    	ResultSet re = dbConnect.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String classtwoac = re.getString("classtwoac");
					beans.add(classtwoac);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbConnect.closeCon();
    	return beans;
    }
    //调出二级热点名称
    public ArrayList<String> getAllclassTwoAcByName(String classoneac)
    {
    	DbConnect dbConnect = new DbConnect();
    	ArrayList<String>  beans = new ArrayList<String>();
    	String sql = "select distinct(classtwoac) from "+strTableName2+" ";
    	String where = " where classoneac = '"+classoneac+"' ";
    	sql = sql+where;
    	ResultSet re = dbConnect.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String classtwoac = re.getString("classtwoac");
					beans.add(classtwoac);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbConnect.closeCon();
    	return beans;
    }
    //统计已上传的图纸个数
    public int getPaperNum(String where)
    {
    	int count = 0;
    	try{
    	DbConnect dbConnect = new DbConnect();
    	String sql = " select count(distinct cadpic) from "+strTableName+" "+where+" and imgext = 1 ";
    	ResultSet re = dbConnect.executeQuery(sql);
    	if(re != null && re.next())
    		count = re.getInt(1);
    	re.close();
    	dbConnect.closeCon();
    	return count;
    	}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
    }


    private String strId;
    private String strAcName;// 二级热点在热点关联表（二级热点表）对应的id
    private String classtwoac;
    private String strCadName;		
    private String strCadPic;			
    private String intFloor;
    private	String strCity;
    private String strCountry;
    private String clssOneAc;
    private String clssTwoAc;
    private String strCreator;//			创建人
    private String dCreatDate;//		创建时间



    public String getStrId() {
        return strId;
    }

    public void setStrId(String strId) {
        this.strId = strId;
    }

    public String getAcName() {
        return strAcName;
    }

    public void setAcName(String strAcName) {
        this.strAcName = strAcName;
    }
    public String getclasstwoac() {
        return classtwoac;
    }

    public void setclasstwoac(String classtwoac) {
        this.classtwoac = classtwoac;
    }
    
    public String getCadName() {
		return strCadName;
	}
    
    public void setCadName(String strCadName){
    	 this.strCadName = strCadName;
    }
    
    public String getCadPic() {
		return strCadPic;
	}
    
    public void setCadPic(String strCadPic){
    	this.strCadPic = strCadPic;
    }
    public String getFloor() {
		return intFloor;
	}
    
    public void setFloor(String intFloor){
    	this.intFloor = intFloor;
    }
   
    public String getStrCity() {
		return strCity;
	}
    
    public void setStrCity(String strCity){
    	this.strCity = strCity;
    }
    public String getStrCountry() {
		return strCountry;
	}
    
    public void setStrCountry(String strCountry){
    	this.strCountry = strCountry;
    }
    public String getClssOneAc() {
		return clssOneAc;
	}
    
    public void setClassOneAc(String clssOneAc){
    	this.clssOneAc = clssOneAc;
    }
    public String getClssTwoAc() {
		return clssTwoAc;
	}
    
    public void setClssTwoAc(String clssTwoAc){
    	this.clssTwoAc = clssTwoAc;
    }
    
 
    public String getStrCreator() {
        return strCreator;
    }

    public void setStrCreator(String strCreator) {
        this.strCreator = strCreator;
    }

    public String getdCreatDate() {
        return dCreatDate;
    }

    public void setdCreatDate(String dCreatDate) {
        this.dCreatDate = dCreatDate;
    }
}
